package jp.ac.u_tokyo.dch.dao.common;

import java.util.ArrayList;
import java.util.List;

import jp.ac.u_tokyo.dch.core.db.CustomPreparedStatement;
import jp.ac.u_tokyo.dch.core.db.CustomResultSet;
import jp.ac.u_tokyo.dch.core.db.DBConnection;
import jp.ac.u_tokyo.dch.core.exceptions.DBException;
import jp.ac.u_tokyo.dch.model.db.DataSchemaDetail;
import jp.ac.u_tokyo.dch.model.db.FieldMaster;
import jp.ac.u_tokyo.dch.util.common.CommonDBConnection;
/**
* @version 1.1
*/
public class FieldMasterDao {

	/**
	 * DBコネクションオブジェクト
	 */
	private DBConnection dbconn = null;

	/**
	 * 生成者
	 *
	 * @param DBコネクションオブジェクト
	 */
	public FieldMasterDao(DBConnection dbconn) {
		this.dbconn = dbconn;
	}


	public List<FieldMaster> selectList() throws DBException {

		List<FieldMaster> dataList = new ArrayList<FieldMaster>();

		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("select * from field_master order by sort ,displayorder");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try {
			ps = dbconn.prepareStatement(sqlQuery.toString());
			rs = ps.executeQuery();

			while (rs.next()) {

				FieldMaster fieldMaster = new FieldMaster();
				fieldMaster.setFieldkey(rs.getString("fieldkey"));
				fieldMaster.setDisplayName(rs.getString("displayname"));
				fieldMaster.setItemName(rs.getString("itemname"));
				fieldMaster.setSort(rs.getString("sort"));

				dataList.add(fieldMaster);
			}

		} catch (DBException dbe) {
			throw dbe;
		} finally {
			CommonDBConnection.closeResource(ps, rs);
		}
		return dataList;
	}

	public List<FieldMaster> selectListByFront() throws DBException {

		List<FieldMaster> dataList = new ArrayList<FieldMaster>();

		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("select * from field_master where sort in ('001','002','003','004','005','006')");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try {
			ps = dbconn.prepareStatement(sqlQuery.toString());
			rs = ps.executeQuery();

			while (rs.next()) {

				FieldMaster fieldMaster = new FieldMaster();
				fieldMaster.setFieldkey(rs.getString("fieldkey"));
				fieldMaster.setDisplayName(rs.getString("displayname"));
				fieldMaster.setItemName(rs.getString("itemname"));
				fieldMaster.setSort(rs.getString("sort"));

				dataList.add(fieldMaster);
			}

		} catch (DBException dbe) {
			throw dbe;
		} finally {
			CommonDBConnection.closeResource(ps, rs);
		}
		return dataList;
	}

	public void insertAll(List<DataSchemaDetail> list) throws DBException {

		CustomPreparedStatement ps = null;

		try{
			String query = "INSERT INTO field_master (fieldkey,displayname,itemname,displayorder,sort)  VALUES (?,?,?,?,?)";
			ps = dbconn.prepareStatement(query);

			for (DataSchemaDetail detail : list) {
				int index = 1;
				ps.setString(index++, detail.getFieldKey());
				ps.setString(index++, detail.getFieldDisplayname());
				ps.setString(index++, detail.getFieldItemName());
				ps.setInt(index++, 0);
				ps.setString(index++, "999");
				ps.addBatch();
			}

			ps.executeBatch();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			if(ps != null) ps.close();
		}
	}

	public void deleteCustom(int schemaid) throws DBException {

		CustomPreparedStatement ps = null;
		try{
			StringBuilder sqlQuery = new StringBuilder();

			sqlQuery.append("delete from field_master where exists(");
			sqlQuery.append("select * from data_schema_detail inner join data_schema on data_schema_detail.schemaid = data_schema.schemaid ");
			sqlQuery.append("where data_schema.schemaid = ? ");
			sqlQuery.append("and data_schema_detail.customsign='1' ");
			sqlQuery.append("and field_master.fieldkey = data_schema_detail.fieldkey)");
			ps = dbconn.prepareStatement(sqlQuery.toString());

			ps.setInt(1, schemaid);
			ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}


	}

	public FieldMaster selectObject(String fieldkey) throws DBException {

		FieldMaster fieldMaster = null;

		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("select * from field_master where fieldkey = ?");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try {
			ps = dbconn.prepareStatement(sqlQuery.toString());
			ps.setString(1, fieldkey);
			rs = ps.executeQuery();

			if (rs.next()) {

				fieldMaster = new FieldMaster();
				fieldMaster.setFieldkey(rs.getString("fieldkey"));
				fieldMaster.setDisplayName(rs.getString("displayname"));
				fieldMaster.setItemName(rs.getString("itemname"));
				fieldMaster.setSort(rs.getString("sort"));
			}

		} catch (DBException dbe) {
			throw dbe;
		} finally {
			CommonDBConnection.closeResource(ps, rs);
		}
		return fieldMaster;
	}
}
